﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using PPAP.Model;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class PPINFODAO
    {
        public PPINFODAO()
		{}
		#region  成员方法

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(PPINFO model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "PPINFO");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into PPINFO(");
            strSql.Append("ID,SupplierID,PartID,Status)");
            strSql.Append(" values (");
            strSql.Append(":ID,:SupplierID,:PartID,:Status)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":SUPPLIERID", OracleType.Number,4),
					new OracleParameter(":PARTID", OracleType.Number,4),
					new OracleParameter(":STATUS", OracleType.Number,4)
					};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.SUPPLIERID;
            parameters[2].Value = model.PARTID;
            parameters[3].Value = model.STATUS;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?model.ID:0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(PPINFO model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PPINFO set ");
            strSql.Append("ID=:ID,");
            strSql.Append("SUPPLIERID=:SUPPLIERID,");
            strSql.Append("PARTID=:PARTID,");
            strSql.Append("PPAPNO=:PPAPNO,");
            strSql.Append("STATUS=:STATUS,");
            strSql.Append("PLANT=:PLANT,");
            strSql.Append("BDNTREVISIONNO=:BDNTREVISIONNO,");
            strSql.Append("BDNTREVISIONSTATUS=:BDNTREVISIONSTATUS,");
            strSql.Append("ECUHWVERSIONNO=:ECUHWVERSIONNO,");
            strSql.Append("ECUSWVERSIONNO=:ECUSWVERSIONNO,");
            strSql.Append("SAMPLINGFORM=:SAMPLINGFORM,");
            strSql.Append("SAMPLINGTYPE=:SAMPLINGTYPE,");
            strSql.Append("SAMPLINGMODE=:SAMPLINGMODE,");
            strSql.Append("DELIVEREDPARTSQ=:DELIVEREDPARTSQ,");
            strSql.Append("MODIFICATIONSCOPE=:MODIFICATIONSCOPE,");
            strSql.Append("REMARKS=:REMARKS,");
            strSql.Append("SUBMITTIME=:SUBMITTIME,");
            strSql.Append("TESTINGRESULT=:TESTINGRESULT,");
            strSql.Append("PARTRELATEDRESULT=:PARTRELATEDRESULT,");
            strSql.Append("PROCESSRELATEDRESULT=:PROCESSRELATEDRESULT,");
            strSql.Append("FINALDECISION=:FINALDECISION,");
            strSql.Append("COLORDECISION=:COLORDECISION,");
            strSql.Append("SSA=:SSA,");
            strSql.Append("TESTINGPERSON=:TESTINGPERSON,");
            strSql.Append("TESTINGTIME=:TESTINGTIME,");
            strSql.Append("STARTTIME=:STARTTIME,");
            strSql.Append("ENDTIME=:ENDTIME,");
            strSql.Append("QMINSPECTOR=:QMINSPECTOR,");
            strSql.Append("SAMPLEREPORTAUTHOR=:SAMPLEREPORTAUTHOR,");
            strSql.Append("LOGFILE=:LOGFILE,");
            strSql.Append("COSTCHARGE=:COSTCHARGE,");
            strSql.Append("FJID=:FJID,");
            strSql.Append("EXPRESSNO=:EXPRESSNO,");
            strSql.Append("PLANNEDISIRDATE=:PLANNEDISIRDATE");
            //strSql.Append("ACTUALISIRDATE=:ACTUALISIRDATE,");
            //strSql.Append("QMREMARKS=:QMREMARKS,");
            //strSql.Append("FINALREMARKS=:FINALREMARKS,");
            //strSql.Append("SAMPLEREPORTFJID=:SAMPLEREPORTFJID");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":SUPPLIERID", OracleType.Number,4),
					new OracleParameter(":PARTID", OracleType.Number,4),
					new OracleParameter(":PPAPNO", OracleType.VarChar,20),
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":PLANT", OracleType.VarChar,50),
					new OracleParameter(":BDNTREVISIONNO", OracleType.VarChar,20),
					new OracleParameter(":BDNTREVISIONSTATUS", OracleType.VarChar,20),
					new OracleParameter(":ECUHWVERSIONNO", OracleType.VarChar,20),
					new OracleParameter(":ECUSWVERSIONNO", OracleType.VarChar,20),
					new OracleParameter(":SAMPLINGFORM", OracleType.VarChar,50),
					new OracleParameter(":SAMPLINGTYPE", OracleType.VarChar,20),
					new OracleParameter(":SAMPLINGMODE", OracleType.VarChar,20),
					new OracleParameter(":DELIVEREDPARTSQ", OracleType.VarChar,50),
					new OracleParameter(":MODIFICATIONSCOPE", OracleType.VarChar,50),
					new OracleParameter(":REMARKS", OracleType.VarChar,500),
					new OracleParameter(":SUBMITTIME", OracleType.DateTime),
					new OracleParameter(":TESTINGRESULT", OracleType.VarChar,20),
					new OracleParameter(":PARTRELATEDRESULT", OracleType.VarChar,50),
					new OracleParameter(":PROCESSRELATEDRESULT", OracleType.VarChar,50),
					new OracleParameter(":FINALDECISION", OracleType.VarChar,50),
					new OracleParameter(":COLORDECISION", OracleType.VarChar,50),
					new OracleParameter(":SSA", OracleType.VarChar,50),
					new OracleParameter(":TESTINGPERSON", OracleType.VarChar,20),
					new OracleParameter(":TESTINGTIME", OracleType.DateTime),
					new OracleParameter(":STARTTIME", OracleType.DateTime),
					new OracleParameter(":ENDTIME", OracleType.DateTime),
					new OracleParameter(":QMINSPECTOR", OracleType.VarChar,20),
					new OracleParameter(":SAMPLEREPORTAUTHOR", OracleType.VarChar,20),
					new OracleParameter(":LOGFILE", OracleType.VarChar,50),
					new OracleParameter(":COSTCHARGE", OracleType.VarChar,50),
					new OracleParameter(":FJID", OracleType.VarChar,50),
					new OracleParameter(":EXPRESSNO", OracleType.VarChar,50),
					new OracleParameter(":PLANNEDISIRDATE", OracleType.DateTime)
                    //new OracleParameter(":ACTUALISIRDATE", OracleType.DateTime),
                    //new OracleParameter(":QMREMARKS", OracleType.VarChar,500),
                    //new OracleParameter(":FINALREMARKS", OracleType.VarChar,150),
                    //new OracleParameter(":SAMPLEREPORTFJID", OracleType.VarChar,50)
                                           };
            parameters[0].Value = model.ID;
            parameters[1].Value = model.SUPPLIERID;
            parameters[2].Value = model.PARTID;
            parameters[3].Value = model.PPAPNO;
            parameters[4].Value = model.STATUS;
            parameters[5].Value = model.PLANT;
            parameters[6].Value = model.BDNTREVISIONNO;
            parameters[7].Value = model.BDNTREVISIONSTATUS;
            parameters[8].Value = model.ECUHWVERSIONNO;
            parameters[9].Value = model.ECUSWVERSIONNO;
            parameters[10].Value = model.SAMPLINGFORM;
            parameters[11].Value = model.SAMPLINGTYPE;
            parameters[12].Value = model.SAMPLINGMODE;
            parameters[13].Value = model.DELIVEREDPARTSQ;
            parameters[14].Value = model.MODIFICATIONSCOPE;
            parameters[15].Value = model.REMARKS;
            parameters[16].Value = model.SUBMITTIME;
            parameters[17].Value = model.TESTINGRESULT;
            parameters[18].Value = model.PARTRELATEDRESULT;
            parameters[19].Value = model.PROCESSRELATEDRESULT;
            parameters[20].Value = model.FINALDECISION;
            parameters[21].Value = model.COLORDECISION;
            parameters[22].Value = model.SSA;
            parameters[23].Value = model.TESTINGPERSON;
            parameters[24].Value = model.TESTINGTIME;
            parameters[25].Value = model.STARTTIME;
            parameters[26].Value = model.ENDTIME;
            parameters[27].Value = model.QMINSPECTOR;
            parameters[28].Value = model.SAMPLEREPORTAUTHOR;
            parameters[29].Value = model.LOGFILE;
            parameters[30].Value = model.COSTCHARGE;
            parameters[31].Value = model.FJID;
            parameters[32].Value = model.EXPRESSNO;
            parameters[33].Value = model.PLANNEDISIRDATE;
            //parameters[34].Value = model.ACTUALISIRDATE;
            //parameters[35].Value = model.QMREMARKS;
            //parameters[36].Value = model.FINALREMARKS;
            //parameters[37].Value = model.SAMPLEREPORTFJID;

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from PPINFO ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PPINFO GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,SUPPLIERID,PARTID,PPAPNO,STATUS,PLANT,BDNTREVISIONNO,BDNTREVISIONSTATUS,ECUHWVERSIONNO,ECUSWVERSIONNO,SAMPLINGFORM,SAMPLINGTYPE,SAMPLINGMODE,DELIVEREDPARTSQ,MODIFICATIONSCOPE,REMARKS,SUBMITTIME,TESTINGRESULT,PARTRELATEDRESULT,PROCESSRELATEDRESULT,FINALDECISION,COLORDECISION,SSA,TESTINGPERSON,TESTINGTIME,STARTTIME,ENDTIME,QMINSPECTOR,SAMPLEREPORTAUTHOR,LOGFILE,COSTCHARGE,FJID,EXPRESSNO,PLANNEDISIRDATE,ACTUALISIRDATE,QMREMARKS,FINALREMARKS,SAMPLEREPORTFJID from PPINFO ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

            PPINFO model = new PPINFO();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SUPPLIERID"].ToString() != "")
                {
                    model.SUPPLIERID = int.Parse(ds.Tables[0].Rows[0]["SUPPLIERID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PARTID"].ToString() != "")
                {
                    model.PARTID = int.Parse(ds.Tables[0].Rows[0]["PARTID"].ToString());
                }
                model.PPAPNO = ds.Tables[0].Rows[0]["PPAPNO"].ToString();
                if (ds.Tables[0].Rows[0]["STATUS"].ToString() != "")
                {
                    model.STATUS = int.Parse(ds.Tables[0].Rows[0]["STATUS"].ToString());
                }
                model.PLANT = ds.Tables[0].Rows[0]["PLANT"].ToString();
                model.BDNTREVISIONNO = ds.Tables[0].Rows[0]["BDNTREVISIONNO"].ToString();
                model.BDNTREVISIONSTATUS = ds.Tables[0].Rows[0]["BDNTREVISIONSTATUS"].ToString();
                model.ECUHWVERSIONNO = ds.Tables[0].Rows[0]["ECUHWVERSIONNO"].ToString();
                model.ECUSWVERSIONNO = ds.Tables[0].Rows[0]["ECUSWVERSIONNO"].ToString();
                model.SAMPLINGFORM = ds.Tables[0].Rows[0]["SAMPLINGFORM"].ToString();
                model.SAMPLINGTYPE = ds.Tables[0].Rows[0]["SAMPLINGTYPE"].ToString();
                model.SAMPLINGMODE = ds.Tables[0].Rows[0]["SAMPLINGMODE"].ToString();
                model.DELIVEREDPARTSQ = ds.Tables[0].Rows[0]["DELIVEREDPARTSQ"].ToString();
                model.MODIFICATIONSCOPE = ds.Tables[0].Rows[0]["MODIFICATIONSCOPE"].ToString();
                model.REMARKS = ds.Tables[0].Rows[0]["REMARKS"].ToString();
                if (ds.Tables[0].Rows[0]["SUBMITTIME"].ToString() != "")
                {
                    model.SUBMITTIME = DateTime.Parse(ds.Tables[0].Rows[0]["SUBMITTIME"].ToString());
                }
                model.TESTINGRESULT = ds.Tables[0].Rows[0]["TESTINGRESULT"].ToString();
                model.PARTRELATEDRESULT = ds.Tables[0].Rows[0]["PARTRELATEDRESULT"].ToString();
                model.PROCESSRELATEDRESULT = ds.Tables[0].Rows[0]["PROCESSRELATEDRESULT"].ToString();
                model.FINALDECISION = ds.Tables[0].Rows[0]["FINALDECISION"].ToString();
                model.COLORDECISION = ds.Tables[0].Rows[0]["COLORDECISION"].ToString();
                model.SSA = ds.Tables[0].Rows[0]["SSA"].ToString();
                model.TESTINGPERSON = ds.Tables[0].Rows[0]["TESTINGPERSON"].ToString();
                if (ds.Tables[0].Rows[0]["TESTINGTIME"].ToString() != "")
                {
                    model.TESTINGTIME = DateTime.Parse(ds.Tables[0].Rows[0]["TESTINGTIME"].ToString());
                }
                if (ds.Tables[0].Rows[0]["STARTTIME"].ToString() != "")
                {
                    model.STARTTIME = DateTime.Parse(ds.Tables[0].Rows[0]["STARTTIME"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ENDTIME"].ToString() != "")
                {
                    model.ENDTIME = DateTime.Parse(ds.Tables[0].Rows[0]["ENDTIME"].ToString());
                }
                model.QMINSPECTOR = ds.Tables[0].Rows[0]["QMINSPECTOR"].ToString();
                model.SAMPLEREPORTAUTHOR = ds.Tables[0].Rows[0]["SAMPLEREPORTAUTHOR"].ToString();
                model.LOGFILE = ds.Tables[0].Rows[0]["LOGFILE"].ToString();
                model.COSTCHARGE = ds.Tables[0].Rows[0]["COSTCHARGE"].ToString();
                model.FJID = ds.Tables[0].Rows[0]["FJID"].ToString();
                model.EXPRESSNO = ds.Tables[0].Rows[0]["EXPRESSNO"].ToString();
                if (ds.Tables[0].Rows[0]["PLANNEDISIRDATE"].ToString() != "")
                {
                    model.PLANNEDISIRDATE = DateTime.Parse(ds.Tables[0].Rows[0]["PLANNEDISIRDATE"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ACTUALISIRDATE"].ToString() != "")
                {
                    model.ACTUALISIRDATE = DateTime.Parse(ds.Tables[0].Rows[0]["ACTUALISIRDATE"].ToString());
                }
                model.QMREMARKS = ds.Tables[0].Rows[0]["QMREMARKS"].ToString();
                model.FINALREMARKS = ds.Tables[0].Rows[0]["FINALREMARKS"].ToString();
                model.SAMPLEREPORTFJID = ds.Tables[0].Rows[0]["SAMPLEREPORTFJID"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,SUPPLIERID,PARTID,PPAPNO,STATUS,PLANT,BDNTREVISIONNO,BDNTREVISIONSTATUS,ECUHWVERSIONNO,ECUSWVERSIONNO,SAMPLINGFORM,SAMPLINGTYPE,SAMPLINGMODE,DELIVEREDPARTSQ,MODIFICATIONSCOPE,REMARKS,SUBMITTIME,TESTINGRESULT,PARTRELATEDRESULT,PROCESSRELATEDRESULT,FINALDECISION,COLORDECISION,SSA,TESTINGPERSON,TESTINGTIME,STARTTIME,ENDTIME,QMINSPECTOR,SAMPLEREPORTAUTHOR,LOGFILE,COSTCHARGE,FJID,EXPRESSNO,PLANNEDISIRDATE,ACTUALISIRDATE,QMREMARKS,FINALREMARKS,SAMPLEREPORTFJID ");
            strSql.Append(" FROM PPINFO ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOra.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            OracleParameter[] parameters = {
                    new OracleParameter(":tblName", OracleType.VarChar, 255),
                    new OracleParameter(":fldName", OracleType.VarChar, 255),
                    new OracleParameter(":PageSize", OracleType.Number),
                    new OracleParameter(":PageIndex", OracleType.Number),
                    new OracleParameter(":IsReCount", OracleType.Clob),
                    new OracleParameter(":OrderType", OracleType.Clob),
                    new OracleParameter(":strWhere", OracleType.VarChar,1000),
                    };
            parameters[0].Value = "PPINFO";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperOra.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        public string GetSupplierByPartID(int id)
        {
            string sql = "SELECT SupplierID FROM PPINFO WHERE PartID="+id;
            DataSet ds = DbHelperOra.Query(sql);
            string Suppliers = "";
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Suppliers += dr[0].ToString() + ",";
            }
            return Suppliers;
        }

        ///// <summary>
        ///// 获得数据列表
        ///// </summary>
        //public DataSet GetList(string Orderstr, string strWhere, int PageSize, int CurrentPage)
        //{
        //    SqlParameter[] Params = { new SqlParameter("@select", "*"), new SqlParameter("@order", Orderstr), new SqlParameter("@where", "" + strWhere), new SqlParameter("@table", " GetPPAPSupplier"), new SqlParameter("@CurrentPage", CurrentPage), new SqlParameter("@PageSize", PageSize) };
        //    return AccessHelper.GetSearchResult("SearchResult", Params, CommandType.StoredProcedure);
        //}

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM PPINFO ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 分配任务
        /// </summary>
        /// <param name="ids">产品ID</param>
        /// <param name="tid">测试人员ID</param>
        /// <returns></returns>
        public int FP(string ids,string tid)
        {
            string sql = "UPDATE dbo.PPINFO SET TESTINGPERSON='" + tid + "',TESTINGTIME=GETDATE(),STATUS=2 WHERE ID IN (" + ids + ")";
            return DbHelperOra.ExecuteSql(sql);
        }

        /// <summary>
        /// 待初审
        /// </summary>
        /// <returns></returns>
        public DataSet Getpp()
        {
            string sql = @"SELECT p.*,b.BDNTPartName,b.BDNTPartNo,b.CreateDate,
                            b.CreateUser,b.ELevel,b.PartColor,b.PlannedISIRDate,case status when 1 then 'Sample-Received' else 'PPAP start' end Statu,
                            b.QLevel,b.SubmissionLevel,s.SUPPLIERNAME AS supplier FROM dbo.PPINFO p
                            LEFT JOIN dbo.PartBaseInfo b
                            ON p.PartID=b.ID
                            LEFT JOIN dbo.SUPPLIER s
                            ON p.SupplierID=s.ID
                            WHERE p.Status=1 or p.status=7";
            return DbHelperOra.Query(sql);

        }

        /// <summary>
        /// 待测试
        /// </summary>
        /// <returns></returns>
        public DataSet GetppForTest(string UserNo)
        {
            string sql = @"SELECT *,t.id as Tid FROM dbo.PPINFO p
                        LEFT JOIN dbo.PartBaseInfo b ON b.ID=p.PartID
                        LEFT JOIN dbo.SUPPLIER s ON p.SupplierID=s.ID
                        LEFT JOIN dbo.TestingItem t ON t.PPID=p.ID
                        LEFT JOIN dbo.TESTINGUSER u ON t.TestingPerson=u.ID
                        WHERE p.Status=2  AND t.Status=1 AND  t.TestingPerson='" + UserNo + "' ";
            return DbHelperOra.Query(sql);

        }

        /// <summary>
        /// 测试查询
        /// </summary>
        /// <returns></returns>
        public DataSet GetppForTestAll(string UserNo)
        {
            string sql = @"SELECT *,t.id as Tid FROM dbo.PPINFO p
                        LEFT JOIN dbo.PartBaseInfo b ON b.ID=p.PartID
                        LEFT JOIN dbo.SUPPLIER s ON p.SupplierID=s.ID
                        LEFT JOIN dbo.TestingItem t ON t.PPID=p.ID
                        LEFT JOIN dbo.TESTINGUSER u ON t.TestingPerson=u.ID
                        WHERE  t.TestingPerson='" + UserNo + "' ";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 待测试
        /// </summary>
        /// <returns></returns>
        public DataSet GetppForTestByID(string id)
        {
            string sql = @"SELECT *,t.Status S FROM dbo.PPINFO p
                        LEFT JOIN dbo.PartBaseInfo b ON b.ID=p.PartID
                        LEFT JOIN dbo.SUPPLIER s ON p.SupplierID=s.ID
                        LEFT JOIN dbo.TestingItem t ON t.PPID=p.ID
                        LEFT JOIN dbo.TESTINGUSER u ON t.TestingPerson=u.ID
                        WHERE  t.id= " +id;
            return DbHelperOra.Query(sql);

        }

        /// <summary>
        /// 获取最终决定数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetppForFinal()
        {
            string sql = @"SELECT p.*,b.BDNTPartName,b.BDNTPartNo,b.CreateDate,
                            b.CreateUser,b.ELevel,b.PartColor,b.PlannedISIRDate,case p.status when 2 then 'sample - in testing' else 'PPAP start' end Statu,
                            b.QLevel,b.SubmissionLevel,s.SUPPLIERNAME AS supplier FROM dbo.PPINFO p
                            LEFT JOIN dbo.PartBaseInfo b
                            ON p.PartID=b.ID
                            LEFT JOIN dbo.SUPPLIER s
                            ON p.SupplierID=s.ID
                            WHERE p.Status=2 ";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获取待初审详情
        /// </summary>
        /// <returns></returns>
        public DataTable GetppByID(string ID)
        {
            string sql = @"SELECT p.*,b.*,s.*,
CASE ELevel WHEN 0 THEN '否' ELSE  '是' END E,
CASE QLevel WHEN 0 THEN '否' ELSE  '是' END Q,
'Level'+b.SubmissionLevel SL  FROM dbo.PPINFO p
                            LEFT JOIN dbo.PartBaseInfo b
                            ON p.PartID=b.ID
                            LEFT JOIN dbo.SUPPLIER s
                            ON p.SupplierID=s.ID
                            WHERE p.ID=" + ID;
            return DbHelperOra.Query(sql).Tables[0];
        }

        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int UpdateStatus(int id,int status,string sqlwhere)
        {
            string sql = "UPDATE dbo.PPINFO SET STATUS="+status+" WHERE ID="+id;
            if (sqlwhere != null)
                sql = sqlwhere;
            return DbHelperOra.ExecuteSql(sql);
        }

        /// <summary>
        /// QM上传附件
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="SampleReportFJID"></param>
        /// <returns></returns>
        public int UpSampleReportFJID(string ID, string SampleReportFJID)
        {
            string sql = "UPDATE dbo.PPINFO SET SampleReportFJID='"+SampleReportFJID+"' WHERE id="+ID;
            return DbHelperOra.ExecuteSql(sql);
        }

        /// <summary>
        /// 获取所有附件信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllFile()
        {
            string sql = @"SELECT F_FileName,F_url,UpTime,TestingItemName,TESTINGNAME,1 AS B,b.BDNTPartName FROM dbo.T_FileTable t
                            LEFT JOIN dbo.TestingItem i ON t.F_FileID=i.AttachmentID
                            LEFT JOIN dbo.TESTINGUSER u ON u.ID=i.TestingPerson
                            LEFT JOIN dbo.PartBaseInfo b ON b.ID=i.PPID
                            WHERE i.Status IS NOT NULL";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获取供应商组件发布信息
        /// </summary>
        /// <param name="userno"></param>
        /// <returns></returns>
        public DataSet GetQYList(string userno,int pageindex,int pagesize)
        {
            string sql = string.Format(@"SELECT * FROM (
                SELECT A.*, ROWNUM RN FROM(
                SELECT p.id,p.status,b.BDNTPartName,b.BDNTPartNo,b.CreateDate,
                                            b.CreateUser,b.ELevel,b.PartColor,b.PlannedISIRDate,
                                            b.QLevel,b.SubmissionLevel FROM PPINFO p
                                            LEFT JOIN PartBaseInfo b
                                            ON p.PartID=b.ID
                                            WHERE p.SupplierID={0})A
                                            WHERE ROWNUM<={2}
                                            )WHERE RN>{1}", userno,pageindex*pagesize,(pageindex+1)*pagesize);
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获取供应商组件发布信息条数
        /// </summary>
        /// <param name="userno"></param>
        /// <returns></returns>
        public int  GetQYList(string userno)
        {
            string sql = @"SELECT count(*) FROM PPINFO p
                            LEFT JOIN PartBaseInfo b
                            ON p.PartID=b.ID
                            WHERE p.SupplierID=" + userno;
            return Convert.ToInt32(DbHelperOra.Query(sql).Tables[0].Rows[0][0]);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PPINFO GetModelByPPID(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT p.*,b.BDNTPartName,b.BDNTPartNo,b.CreateDate,
                            b.CreateUser,b.ELevel,b.PartColor,b.PlannedISIRDate pdate,
                            b.QLevel,b.SubmissionLevel FROM PPINFO p
                            LEFT JOIN PartBaseInfo b
                            ON p.PartID=b.ID
                            WHERE p.ID="+ID);

            PPINFO model = new PPINFO();
            DataSet ds = DbHelperOra.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                model.Bdntpartno = ds.Tables[0].Rows[0]["BDNTPartNo"].ToString();
                model.Bdntpartname = ds.Tables[0].Rows[0]["BDNTPartName"].ToString();
                model.Partcolor = ds.Tables[0].Rows[0]["PartColor"].ToString();
                model.Elevel = ds.Tables[0].Rows[0]["ELevel"].ToString()=="1"?true:false;
                model.Qlevel = ds.Tables[0].Rows[0]["QLevel"].ToString()=="1"?true:false;
                if (ds.Tables[0].Rows[0]["PlannedISIRDate"].ToString() != "")
                {
                    model.PLANNEDISIRDATE = DateTime.Parse(ds.Tables[0].Rows[0]["PlannedISIRDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CreateDate"].ToString() != "")
                {
                    model.Createdate = DateTime.Parse(ds.Tables[0].Rows[0]["CreateDate"].ToString());
                }
                model.Createuser = ds.Tables[0].Rows[0]["CreateUser"].ToString();
                model.Submissionlevel = ds.Tables[0].Rows[0]["SubmissionLevel"].ToString();
                ///

                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SupplierID"].ToString() != "")
                {
                    model.SUPPLIERID = int.Parse(ds.Tables[0].Rows[0]["SupplierID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PartID"].ToString() != "")
                {
                    model.PARTID = int.Parse(ds.Tables[0].Rows[0]["PartID"].ToString());
                }
                model.PPAPNO = ds.Tables[0].Rows[0]["PPAPNo"].ToString();
                if (ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.STATUS = int.Parse(ds.Tables[0].Rows[0]["Status"].ToString());
                }
                model.PLANT = ds.Tables[0].Rows[0]["Plant"].ToString();
                model.BDNTREVISIONNO = ds.Tables[0].Rows[0]["BDNTRevisionNo"].ToString();
                model.BDNTREVISIONSTATUS = ds.Tables[0].Rows[0]["BDNTRevisionStatus"].ToString();
                model.ECUHWVERSIONNO = ds.Tables[0].Rows[0]["ECUHWVersionNo"].ToString();
                model.ECUSWVERSIONNO = ds.Tables[0].Rows[0]["ECUSWVersionNo"].ToString();
                model.SAMPLINGFORM = ds.Tables[0].Rows[0]["SamplingForm"].ToString();
                model.SAMPLINGTYPE = ds.Tables[0].Rows[0]["SamplingType"].ToString();
                model.SAMPLINGMODE = ds.Tables[0].Rows[0]["SamplingMode"].ToString();
                model.DELIVEREDPARTSQ = ds.Tables[0].Rows[0]["DeliveredPartsQ"].ToString();
                model.MODIFICATIONSCOPE = ds.Tables[0].Rows[0]["ModificationScope"].ToString();
                model.REMARKS = ds.Tables[0].Rows[0]["Remarks"].ToString();
                if (ds.Tables[0].Rows[0]["SubmitTime"].ToString() != "")
                {
                    model.SUBMITTIME = DateTime.Parse(ds.Tables[0].Rows[0]["SubmitTime"].ToString());
                }
                model.TESTINGRESULT = ds.Tables[0].Rows[0]["TestingResult"].ToString();
                model.PARTRELATEDRESULT = ds.Tables[0].Rows[0]["PartRelatedResult"].ToString();
                model.PROCESSRELATEDRESULT = ds.Tables[0].Rows[0]["ProcessRelatedResult"].ToString();
                model.FINALDECISION = ds.Tables[0].Rows[0]["FinalDecision"].ToString();
                model.COLORDECISION = ds.Tables[0].Rows[0]["ColorDecision"].ToString();
                model.SSA = ds.Tables[0].Rows[0]["SSA"].ToString();
                model.TESTINGPERSON = ds.Tables[0].Rows[0]["TestingPerson"].ToString();
                if (ds.Tables[0].Rows[0]["TestingTime"].ToString() != "")
                {
                    model.TESTINGTIME = DateTime.Parse(ds.Tables[0].Rows[0]["TestingTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartTime"].ToString() != "")
                {
                    model.STARTTIME = DateTime.Parse(ds.Tables[0].Rows[0]["StartTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndTime"].ToString() != "")
                {
                    model.ENDTIME = DateTime.Parse(ds.Tables[0].Rows[0]["EndTime"].ToString());
                }
                model.QMINSPECTOR = ds.Tables[0].Rows[0]["QMInspector"].ToString();
                model.SAMPLEREPORTAUTHOR = ds.Tables[0].Rows[0]["SampleReportAuthor"].ToString();
                model.LOGFILE = ds.Tables[0].Rows[0]["LogFile"].ToString();
                model.COSTCHARGE = ds.Tables[0].Rows[0]["CostCharge"].ToString();
                model.FJID = ds.Tables[0].Rows[0]["FJID"].ToString();
                model.EXPRESSNO = ds.Tables[0].Rows[0]["ExpressNo"].ToString();
                model.QMREMARKS = ds.Tables[0].Rows[0]["QMRemarks"].ToString();
                model.FINALREMARKS = ds.Tables[0].Rows[0]["FinalRemarks"].ToString();
                model.SAMPLEREPORTFJID = ds.Tables[0].Rows[0]["SampleReportFJID"].ToString();
                if (ds.Tables[0].Rows[0]["pdate"].ToString() != "")
                {
                    model.PLANNEDISIRDATE = DateTime.Parse(ds.Tables[0].Rows[0]["pdate"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

		#endregion  成员方法
	}
}
